﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Contacts.Data.Model;
using System.Configuration;

namespace Contacts.Data.Data
{
   public class ContactsDataPersons
   {
      private string _appconfConnString = ConfigurationManager.AppSettings["DatabaseConnection"];

      public List<Persons> LoadAllPersons()
      {
         SqlConnection sqlConnectionToContactsDB = new SqlConnection(_appconfConnString);
          sqlConnectionToContactsDB.Open();
         SqlCommand sqlCommandSelectAll = new SqlCommand("spSelectAllPersons", sqlConnectionToContactsDB);
         sqlCommandSelectAll.CommandType = CommandType.StoredProcedure;
         DataTable dt = new DataTable();
         SqlDataAdapter da = new SqlDataAdapter(sqlCommandSelectAll);
         da.Fill(dt);

         List<Persons> persons = new List<Persons>();
         foreach (DataRow row in dt.Rows)
         {
            Persons personColumnNames = new Persons();
            personColumnNames.PersonID = Convert.ToInt32(row["PersonID"]);
            personColumnNames.FirstName = row["FirstName"].ToString();
            personColumnNames.LastName = row["LastName"].ToString();
            personColumnNames.Phone = row["Phone"].ToString();
            personColumnNames.Email = row["Email"].ToString();
            personColumnNames.Birthday = Convert.ToDateTime(row["Birthday"]);
            personColumnNames.Photo = row["Photo"].ToString();
            personColumnNames.GroupID = Convert.ToInt32(row["GroupID"]);
            persons.Add(personColumnNames);
         }
         sqlConnectionToContactsDB.Close();
         return persons;
      }

      public void SavePerson(int persID, string firstName, string lastName, string phone, string email, string birthday, string photo, int groupID)
      {
         SqlConnection conn = new SqlConnection(_appconfConnString);
         conn.Open();
         SqlCommand comm;

         if (persID == 0)
         {
            comm = new SqlCommand("spAddPerson", conn);
         }
         else
         {
            comm = new SqlCommand("spUpdatePerson", conn);
            comm.Parameters.AddWithValue("@PersonID", persID);
         }

         comm.CommandType = CommandType.StoredProcedure;
         comm.Parameters.AddWithValue("@FirstName", firstName);
         comm.Parameters.AddWithValue("@LastName", lastName);
         comm.Parameters.AddWithValue("@Phone", phone);
         comm.Parameters.AddWithValue("@Email", email);
         comm.Parameters.AddWithValue("@Birthday", birthday);
         comm.Parameters.AddWithValue("@Photo", photo);
         comm.Parameters.AddWithValue("@GroupID", groupID);
         comm.ExecuteNonQuery();
         conn.Close();
      }

      public void DeletePerson(int ID)
      {
         Persons personID = new Persons();
         personID.PersonID = ID;
         SqlConnection con = new SqlConnection(_appconfConnString);
         con.Open();
         SqlCommand comm = new SqlCommand("spDeletePerson", con);
         comm.CommandType = CommandType.StoredProcedure;
         comm.Parameters.AddWithValue("@PersonID", personID.PersonID);
         comm.ExecuteNonQuery();
         con.Close();
      }



      #region Доп:
      public List<Persons> LoadAllPersonsAndGroups()
      {
         SqlConnection sqlConnectionToContactsDB = new SqlConnection(_appconfConnString);
         sqlConnectionToContactsDB.Open();
         SqlCommand sqlCommandSelectAll = new SqlCommand("spSelectAllPersonsAndGroupsRelByGroupID", sqlConnectionToContactsDB);
         sqlCommandSelectAll.CommandType = CommandType.StoredProcedure;
         DataTable dt = new DataTable();
         SqlDataAdapter da = new SqlDataAdapter(sqlCommandSelectAll);
         da.Fill(dt);

         List<Persons> persons = new List<Persons>();
         foreach (DataRow row in dt.Rows)
         {
            Persons personColumnNames = new Persons();
            personColumnNames.PersonID = Convert.ToInt32(row["PersonID"]);
            personColumnNames.FirstName = row["FirstName"].ToString();
            personColumnNames.LastName = row["LastName"].ToString();
            personColumnNames.Phone = row["Phone"].ToString();
            personColumnNames.Email = row["Email"].ToString();
            personColumnNames.Birthday = Convert.ToDateTime(row["Birthday"]);
            personColumnNames.GroupName = row["GroupName"].ToString();
            personColumnNames.Photo = row["Photo"].ToString();
            personColumnNames.GroupID = Convert.ToInt32(row["GroupID"]);

            persons.Add(personColumnNames);
         }
         sqlConnectionToContactsDB.Close();
         return persons;
      }

      public int CountOfPersons(string groupID)
      {
         int countOfPerson;
         SqlConnection conn = new SqlConnection(_appconfConnString);
         conn.Open();
         SqlCommand comm = new SqlCommand("spCountOfPersons", conn);
         comm.CommandType = CommandType.StoredProcedure;
         comm.Parameters.AddWithValue("@GroupID", groupID);
         countOfPerson = (int)comm.ExecuteScalar();

         return countOfPerson;
      }

      #endregion


      #region Search
      public List<Persons> SearchPersonsOfGroup(string name, int groupID)
      {
         SqlConnection sqlConnectionToContactsDB = new SqlConnection(_appconfConnString);
         sqlConnectionToContactsDB.Open();
         SqlCommand sqlCommandSelectAll = new SqlCommand("spSearch", sqlConnectionToContactsDB);
         sqlCommandSelectAll.CommandType = CommandType.StoredProcedure;
         sqlCommandSelectAll.Parameters.AddWithValue("@Name", name);
         sqlCommandSelectAll.Parameters.AddWithValue("@GroupID", groupID);
         DataTable dt = new DataTable();
         SqlDataAdapter da = new SqlDataAdapter(sqlCommandSelectAll);
         da.Fill(dt);

         List<Persons> persons = new List<Persons>();
         foreach (DataRow row in dt.Rows)
         {
            Persons personColumnNames = new Persons();
            personColumnNames.PersonID = Convert.ToInt32(row["PersonID"]);
            personColumnNames.FirstName = row["FirstName"].ToString();
            personColumnNames.LastName = row["LastName"].ToString();
            personColumnNames.Phone = row["Phone"].ToString();
            personColumnNames.Email = row["Email"].ToString();
            personColumnNames.Birthday = Convert.ToDateTime(row["Birthday"]);
            personColumnNames.GroupName = row["GroupName"].ToString();
            personColumnNames.Photo = row["Photo"].ToString();
            personColumnNames.GroupID = Convert.ToInt32(row["GroupID"]);

            persons.Add(personColumnNames);
         }
         sqlConnectionToContactsDB.Close();
         return persons;
      }



      #endregion

   }
}
